﻿<%@ WebHandler Language="C#" Class="MESFeedBackTaskBoard" %>

using System;
using System.IO;
using System.Web;
using Newtonsoft.Json;
using Newtonsoft.Json.Linq;
using Newtonsoft.Json.Converters;
using System.Collections.Generic;
using System.Data;
using System.Text;
using ReportBusiness;
using System.Net;

/*
 * Description: 问题快反看板
 * Copyright (c): 通力凯顿（北京）系统集成有限公司
 * Author: zhunan
 * Date: 2022.7.21
 */

public class MESFeedBackTaskBoard : IHttpHandler
{
    uMESReportOracleDAL dal = new uMESReportOracleDAL();
    UserImage u = new UserImage();
    public bool IsReusable { get { return false; } }
    public void ProcessRequest(HttpContext context)
    {
        string requestjson = "";
        using (StreamReader reader = new StreamReader(context.Request.InputStream)) { requestjson = reader.ReadToEnd(); }
        JObject jObject = JsonConvert.DeserializeObject(requestjson) as JObject;
        if (jObject["type"] != null)
        {
            try
            {
                Result r = new Result();
                context.Response.ContentType = "text/json";
                string type = jObject["type"].ToString();
                switch (type)
                {
                    case "getData":
                        r = getData(jObject);//查询看板数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getAllComment":
                        r = getAllComment(jObject);//查询弹窗数据
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryTotalRate":
                        r = getFactoryTotalRate(jObject);//分厂总处理率
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryRate":
                        r = getFactoryRate(jObject);//各分厂处理率
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getFactoryRateDetail":
                        r = getFactoryRateDetail(jObject);//各分厂处理率明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportFactoryRateDetail":
                        exportFactoryRateDetail(jObject);//各分厂处理率明细--导出
                        break;
                    case "getDepartmentTotalRate":
                        r = getDepartmentTotalRate(jObject);//职能部门问题及时响应率
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDepartmentRate":
                        r = getDepartmentRate(jObject);//各职能部门问题及时响应率
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getDepartmentRateDetail":
                        r = getDepartmentRateDetail(jObject);//各职能部门问题明细
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "exportDepartmentRateDetail":
                        exportDepartmentRateDetail(jObject);//各职能部门问题明细--导出
                        break;
                    case "getLast10Data":
                        r = getLast10Data(jObject);//及时发出率后10名
                        context.Response.Write(JsonConvert.SerializeObject(r));
                        break;
                    case "getExportExcel":
                        getExportExcel(jObject);//导出
                        break;
                    default:
                        break;
                }
            }
            catch (Exception e)
            {
                Result r = new Result();
                r.data = null;
                r.meta = new Result.Meta(500, e.Message);
                r.total = 0;
                context.Response.ContentType = "text/json";
                context.Response.Write(JsonConvert.SerializeObject(r));
            }
        }
    }

    //查询
    Result getData(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT tt.* FROM (
SELECT up.id,
       bpm.tasktitle,
       e.employeename,
       e.fullname,
       case when up.createuser = bpm.userid then  e3.employeeid else e2.employeeid end resemployeeid,
       case when up.createuser = bpm.userid then  e3.employeename else e2.employeename end resemployeename,
       case when up.createuser = bpm.userid then  e3.fullname else e2.fullname end resfullname,
       mp.liuwei,
       case when up.createuser = bpm.userid then  mp3.liuwei else mp2.liuwei end liuwei2,
       up.createdate,
       fpt.problemtype feedback_type,
       up.problem_feedback_title,
       up.problem_status,
       CASE WHEN (bpm.processdefid <> 'obj_a261aa30a89b457f900310f3d3afe86a' AND bpm.processdefid <> 'obj_95ca04cab8e1469bbd258aab2138d3d5') THEN
                   CASE WHEN up.problem_status = '已关闭' THEN '6/6'
                        WHEN up.problem_status <> '已关闭' AND INSTR(bpm.tasktitle,'关闭问题') = 0 THEN
                            CASE WHEN INSTR(bpm.tasktitle,'流程主管') > 0 THEN '1/6' 
                                 WHEN INSTR(bpm.tasktitle,'处室') > 0 THEN '2/6' 
                                 WHEN INSTR(bpm.tasktitle,'副厂') > 0 THEN '3/6'
                                 WHEN INSTR(bpm.tasktitle,'厂级') > 0 THEN '4/6'
                                 WHEN INSTR(bpm.tasktitle,'生产部') > 0 THEN '5/6'
                                 WHEN INSTR(bpm.tasktitle,'公司领导') > 0 THEN '6/6' 
                            END 
                        WHEN up.problem_status <> '已关闭' AND INSTR(bpm.tasktitle,'关闭问题') <> 0 THEN
                            CASE WHEN INSTR(bpm.prevtasktitle,'流程主管') > 0 THEN '1/6' 
                                 WHEN INSTR(bpm.prevtasktitle,'处室') > 0 THEN '2/6' 
                                 WHEN INSTR(bpm.prevtasktitle,'副厂') > 0 THEN '3/6'
                                 WHEN INSTR(bpm.prevtasktitle,'厂级') > 0 THEN '4/6'
                                 WHEN INSTR(bpm.prevtasktitle,'生产部') > 0 THEN '5/6'
                                 WHEN INSTR(bpm.prevtasktitle,'公司领导') > 0 THEN '6/6' 
                            END 
                        END 
            ELSE
               CASE WHEN up.problem_status = '已关闭' THEN '8/8'
                    WHEN up.problem_status <> '已关闭' AND INSTR(bpm.tasktitle,'关闭问题') = 0 THEN
                        CASE WHEN INSTR(bpm.tasktitle,'流程主管') > 0 THEN '1/8' 
                             WHEN INSTR(bpm.tasktitle,'处室') > 0 THEN '2/8' 
                             WHEN INSTR(bpm.tasktitle,'副厂') > 0 THEN '3/8'
                             WHEN INSTR(bpm.tasktitle,'厂级') > 0 THEN '4/8'
                             WHEN INSTR(bpm.tasktitle,'副部') > 0 THEN '5/8'
                             WHEN INSTR(bpm.tasktitle,'部长填写') > 0 THEN '6/8'
                             WHEN INSTR(bpm.tasktitle,'副总经理填写处理意见') > 0 THEN '7/8'
                             WHEN INSTR(bpm.tasktitle,'生产部填写处理意见') > 0 THEN '7/8'
                             WHEN INSTR(bpm.tasktitle,'公司领导填写处理意见') > 0 THEN '8/8' 
                        END 
                        WHEN up.problem_status <> '已关闭' AND INSTR(bpm.tasktitle,'关闭问题') <> 0 THEN
                             CASE WHEN INSTR(bpm.prevtasktitle,'流程主管') > 0 THEN '1/8' 
                                  WHEN INSTR(bpm.prevtasktitle,'处室') > 0 THEN '2/8' 
                                  WHEN INSTR(bpm.prevtasktitle,'副厂') > 0 THEN '3/8'
                                  WHEN INSTR(bpm.prevtasktitle,'厂级') > 0 THEN '4/8'
                                  WHEN INSTR(bpm.prevtasktitle,'副部') > 0 THEN '5/8'
                                  WHEN INSTR(bpm.prevtasktitle,'部长填写') > 0 THEN '6/8'
                                  WHEN INSTR(bpm.prevtasktitle,'副总经理填写处理意见') > 0 THEN '7/8'
                                  WHEN INSTR(bpm.prevtasktitle,'生产部填写处理意见') > 0 THEN '7/8'
                                  WHEN INSTR(bpm.prevtasktitle,'公司领导填写处理意见') > 0 THEN '8/8' 
                             END 
                        END  
                END status,
        CASE WHEN up.problem_status = '已关闭' THEN
                extract(day FROM CAST(up.problem_close_date AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '天' ||
                extract(hour FROM CAST(up.problem_close_date AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '小时' ||
                extract(minute FROM CAST(up.problem_close_date AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '分' ||
                extract(second FROM CAST(up.problem_close_date AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '秒'
            WHEN up.problem_status = '未关闭' THEN
                extract(day FROM CAST(SYSDATE AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '天' ||
                extract(hour FROM CAST(SYSDATE AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '小时' ||
                extract(minute FROM CAST(SYSDATE AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '分' ||
                extract(second FROM CAST(SYSDATE AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '秒'
        END costtime,
        og.factoryname||DECODE(og.workcentername,NULL,'','/' || og.workcentername)|| DECODE(og.shiftname,NULL,'','/' || og.shiftname)|| DECODE(og.teamname,NULL,'','/' || og.teamname) fromorginfo,
        -- 所在单位获取逻辑变更
        CASE WHEN up.createuser = bpm.userid THEN
             og3.factoryname||DECODE(og3.workcentername,NULL,'','/' || og3.workcentername)||DECODE(og3.shiftname,NULL,'','/' || og3.shiftname)|| DECODE(og3.teamname,NULL,'','/' || og3.teamname)
        ELSE
             og2.factoryname||DECODE(og2.workcentername,NULL,'','/' || og2.workcentername)||DECODE(og2.shiftname,NULL,'','/' || og2.shiftname)|| DECODE(og2.teamname,NULL,'','/' || og2.teamname)
        END toorginfo,
        CASE WHEN up.problem_status = '已关闭' THEN  round(up.problem_close_date - up.createdate,6) ELSE round(SYSDATE - up.createdate,6) END TIME,
        CASE WHEN up.problem_status = '已关闭' THEN 1 ELSE 0 END problem__status,
        bpm.processdefid,bpm.userid,up.bindid processinstid,bpm.id taskid
FROM bo_eu_mes_problem_feedback up
LEFT JOIN bo_eu_mes_problem_type fpt ON fpt.id = up.feedback_type
LEFT JOIN employee e ON 'MES_' || e.employeename = up.createuser
LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid 
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid
LEFT JOIN employee e2 ON 'MES_' || e2.employeename = bpm.userid
LEFT JOIN employee e3 ON 'MES_' || e3.employeename = bpm.owner
INNER JOIN bpm_org_view og ON og.USERID = up.createuser
LEFT JOIN bpm_org_view og2 ON og2.USERID = bpm.userid
LEFT JOIN bpm_org_view og3 ON og3.USERID = bpm.owner
LEFT JOIN bpm_orguser_table u ON u.userid = up.createuser --问题提出人
LEFT JOIN mes_userimage_mapping mp ON mp.employeename = e.employeename 
LEFT JOIN mes_userimage_mapping mp2 ON mp2.employeename = e2.employeename 
LEFT JOIN mes_userimage_mapping mp3 ON mp3.employeename = e3.employeename
WHERE up.processdefid IN ('obj_a261aa30a89b457f900310f3d3afe86a','obj_95ca04cab8e1469bbd258aab2138d3d5') 
      AND (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0) 
      AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL) ";

        //提出单位
        if (jObject["fromOrg"] != null && jObject["fromOrg"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["fromOrg"].ToString());
            if (arr.Count == 1)
            {
                sql += string.Format(" AND og.factoryid = '{0}'", arr[0].ToString());
            }
            else if (arr.Count == 2)
            {
                sql += string.Format(" AND og.factoryid = '{0}' AND og.workcenterid = '{1}'", arr[0].ToString(), arr[1].ToString());
            }
            else if (arr.Count == 3)
            {
                sql += string.Format(" AND og.factoryid = '{0}' AND og.workcenterid = '{1}' AND (og.teamid = '{2}' OR og.shiftid = '{3}')",
                    arr[0].ToString(), arr[1].ToString(), arr[2].ToString(), arr[2].ToString());
            }
            else if (arr.Count == 4)
            {
                sql += string.Format(" AND og.factoryid = '{0}' AND og.workcenterid = '{1}' AND og.shiftid = '{2}' AND og.teamid = '{3}'",
                    arr[0].ToString(), arr[1].ToString(), arr[2].ToString(), arr[3].ToString());
            }
        }

        //处理单位
        if (jObject["toOrg"] != null && jObject["toOrg"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["toOrg"].ToString());
            if (arr.Count == 1)
            {
                sql += string.Format(" AND og2.factoryid = '{0}'", arr[0].ToString());
            }
            else if (arr.Count == 2)
            {
                sql += string.Format(" AND og2.factoryid = '{0}' AND og2.workcenterid = '{1}'", arr[0].ToString(), arr[1].ToString());
            }
            else if (arr.Count == 3)
            {
                sql += string.Format(" AND og2.factoryid = '{0}' AND og2.workcenterid = '{1}' AND (og2.teamid = '{2}' OR og2.shiftid = '{3}')",
                    arr[0].ToString(), arr[1].ToString(), arr[2].ToString(), arr[2].ToString());
            }
            else if (arr.Count == 4)
            {
                sql += string.Format(" AND og2.factoryid = '{0}' AND og2.workcenterid = '{1}' AND og2.shiftid = '{2}' AND og2.teamid = '{3}'",
                    arr[0].ToString(), arr[1].ToString(), arr[2].ToString(), arr[3].ToString());
            }
        }

        //自己相关
        if (jObject["self"] != null && jObject["self"].ToString() == "True")
        {
            if (jObject["employeeid"].ToString() != "")
            {
                sql += string.Format(" AND (e.employeeid = '{0}' OR e2.employeeid = '{1}')", jObject["employeeid"].ToString(), jObject["employeeid"].ToString());
            }
        }

        //问题分类
        if (jObject["feedbackType"].ToString() != "")
        {
            sql += string.Format(" AND fpt.problemtype LIKE '%{0}%'", jObject["feedbackType"].ToString());
        }

        //处理状态
        if (jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "处理中")
            {
                sql += string.Format(" AND up.problem_status = '未关闭'");
            }
            if (jObject["status"].ToString() == "已结束")
            {
                sql += string.Format(" AND up.problem_status = '已关闭'");
            }
        }

        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }

        //提出人
        if (jObject["createuser"].ToString() != "")
        {
            sql += string.Format(" AND u.username = '{0}'", jObject["createuser"].ToString());
        }

        //待办人
        if (jObject["dealperson"].ToString() != "")
        {
            sql += string.Format(" AND (CASE WHEN up.createuser = bpm.userid THEN e3.fullname ELSE e2.fullname END) = '{0}'", jObject["dealperson"].ToString());
        }

        sql += ") tt ORDER BY tt.problem__status,tt.time";
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);

        DataTable dt = dto.DBTable;
        dt.Columns.Add("taskurl");
        dt.Columns.Add("percent", typeof(decimal));
        dt.Columns.Add("daibanuserimageurl", typeof(string));//待办人的头像
        dt.Columns.Add("createuserimageurl", typeof(string));//提出人的头像

        u.getUserImage(dt, "daibanuserimageurl", "resemployeename", "liuwei2");//获取待办人的头像
        u.getUserImage(dt, "createuserimageurl", "employeename", "liuwei");//获取提出人的头像

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dt.Rows)
        {
            dr["taskurl"] = string.Format("&user={0}&processInstId={1}&taskInstId={2}", dr["userid"].ToString(), dr["processinstid"].ToString(), dr["taskid"].ToString());
            if (dr["status"].ToString() == "1/8")
            {
                dr["percent"] = 12.5;
            }
            if (dr["status"].ToString() == "2/8")
            {
                dr["percent"] = 25;
            }
            if (dr["status"].ToString() == "3/8")
            {
                dr["percent"] = 37.5;
            }
            if (dr["status"].ToString() == "4/8")
            {
                dr["percent"] = 50;
            }
            if (dr["status"].ToString() == "5/8")
            {
                dr["percent"] = 62.5;
            }
            if (dr["status"].ToString() == "6/8")
            {
                dr["percent"] = 75;
            }
            if (dr["status"].ToString() == "7/8")
            {
                dr["percent"] = 87.5;
            }
            if (dr["status"].ToString() == "8/8")
            {
                dr["percent"] = 100;
            }
            if (dr["status"].ToString() == "1/6")
            {
                dr["percent"] = 16.6;
            }
            if (dr["status"].ToString() == "2/6")
            {
                dr["percent"] = 33.3;
            }
            if (dr["status"].ToString() == "3/6")
            {
                dr["percent"] = 50;
            }
            if (dr["status"].ToString() == "4/6")
            {
                dr["percent"] = 66.6;
            }
            if (dr["status"].ToString() == "5/6")
            {
                dr["percent"] = 83.3;
            }
            if (dr["status"].ToString() == "6/6")
            {
                dr["percent"] = 100;
            }
        }

        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getAllComment(JObject jObject)
    {
        Result r = new Result();
        string sql = @"
SELECT t.id,t.lczg_emp,t.lczg_result,t.lczg_edit_date,
       t.cs_emp,t.cs_result,t.cs_edit_date,
       t.fcj_emp,t.fcj_result,t.fcj_edit_date,
       t.cj_emp,t.cj_result,t.cj_edit_date,
       t.fbz_emp,t.fbz_result,t.fbz_edit_date,
       t.bz_emp,t.bz_result,t.bz_edit_date,
       t.fzjl_emp,t.fzjl_result,t.fzjl_edit_date,
       t.boss_emp,t.boss_result,t.boss_edit_date,
       lczg.employeename lczgname,lczg.liuwei lczgurl,
       chushi.employeename chushiname,chushi.liuwei chushiurl,
       fcj.employeename fcjname,fcj.liuwei fcjurl,
       cj.employeename cjname,cj.liuwei cjurl,
       fbz.employeename fbzname,fbz.liuwei fbzurl,
       bz.employeename bzname,bz.liuwei bzurl,
       fzjl.employeename fzjlname,fzjl.liuwei fzjlurl,
       boss.employeename bossname,boss.liuwei bossurl,
       REPLACE(SUBSTR(t.lczg_emp,14),'>','') lczgfullname,
       REPLACE(SUBSTR(t.cs_emp,14),'>','') chushifullname,
       REPLACE(SUBSTR(t.fcj_emp,14),'>','') fcjfullname,
       REPLACE(SUBSTR(t.cj_emp,14),'>','') cjfullname,
       REPLACE(SUBSTR(t.fbz_emp,14),'>','') fbzfullname,
       REPLACE(SUBSTR(t.bz_emp,14),'>','') bzfullname,
       REPLACE(SUBSTR(t.fzjl_emp,14),'>','') fzjlfullname,
       REPLACE(SUBSTR(t.boss_emp,14),'>','') bossfullname,
       CASE WHEN tc.actionname = '-' THEN tc.createdate ELSE NULL END lczgdate,
       CASE WHEN tc.actionname = '-' THEN '超时自动执行' ELSE NULL END lczgresult,
       CASE WHEN tc2.actionname = '-' THEN tc2.createdate ELSE NULL END chushidate,
       CASE WHEN tc2.actionname = '-' THEN '超时自动执行' ELSE NULL END chushiresult,
       CASE WHEN tc3.actionname = '-' THEN tc3.createdate ELSE NULL END fcjdate,
       CASE WHEN tc3.actionname = '-' THEN '超时自动执行' ELSE NULL END fcjresult,
       CASE WHEN tc4.actionname = '-' THEN tc4.createdate ELSE NULL END cjdate,
       CASE WHEN tc4.actionname = '-' THEN '超时自动执行' ELSE NULL END cjresult,
       CASE WHEN tc5.actionname = '-' THEN tc5.createdate ELSE NULL END fbzdate,
       CASE WHEN tc5.actionname = '-' THEN '超时自动执行' ELSE NULL END fbzresult,
       CASE WHEN tc6.actionname = '-' THEN tc6.createdate ELSE NULL END bzdate,
       CASE WHEN tc6.actionname = '-' THEN '超时自动执行' ELSE NULL END bzresult,
       CASE WHEN tc7.actionname = '-' THEN tc7.createdate ELSE NULL END fzjldate,
       CASE WHEN tc7.actionname = '-' THEN '超时自动执行' ELSE NULL END fzjlresult
FROM bo_eu_mes_problem_feedback t
LEFT JOIN mes_userimage_mapping lczg ON lczg.employeename = SUBSTR(t.lczg_emp,5,8)
LEFT JOIN mes_userimage_mapping chushi ON chushi.employeename = SUBSTR(t.cs_emp,5,8)
LEFT JOIN mes_userimage_mapping fcj ON fcj.employeename = SUBSTR(t.fcj_emp,5,8)
LEFT JOIN mes_userimage_mapping cj ON cj.employeename = SUBSTR(t.cj_emp,5,8)
LEFT JOIN mes_userimage_mapping fbz ON fbz.employeename = SUBSTR(t.fbz_emp,5,8)
LEFT JOIN mes_userimage_mapping bz ON bz.employeename = SUBSTR(t.bz_emp,5,8)
LEFT JOIN mes_userimage_mapping fzjl ON fzjl.employeename = SUBSTR(t.fzjl_emp,5,8)
LEFT JOIN mes_userimage_mapping boss ON boss.employeename = SUBSTR(t.boss_emp,5,8)
LEFT JOIN awsprd.feedback_wfc_comment@bpm195 tc ON tc.processinstid = t.bindid AND tc.activityname = '流程主管人员接收问题'
LEFT JOIN awsprd.feedback_wfc_comment@bpm195 tc2 ON tc2.processinstid = t.bindid AND tc2.activityname = '处室负责人接收问题'
LEFT JOIN awsprd.feedback_wfc_comment@bpm195 tc3 ON tc3.processinstid = t.bindid AND tc3.activityname = '副厂级负责人接收问题'
LEFT JOIN awsprd.feedback_wfc_comment@bpm195 tc4 ON tc4.processinstid = t.bindid AND tc4.activityname = '分厂级负责人填写处理意见'
LEFT JOIN awsprd.feedback_wfc_comment@bpm195 tc5 ON tc5.processinstid = t.bindid AND tc5.activityname = '副部长填写处理意见'
LEFT JOIN awsprd.feedback_wfc_comment@bpm195 tc6 ON tc6.processinstid = t.bindid AND tc6.activityname = '部长填写处理意见'
LEFT JOIN awsprd.feedback_wfc_comment@bpm195 tc7 ON tc7.processinstid = t.bindid AND tc7.activityname = '生产部填写处理意见'
WHERE t.id = '{0}'
";
        sql = string.Format(sql, jObject["id"].ToString());
        DataTable dt = dal.GetDataTable(sql);

        dt.Columns.Add("lczgimageurl", typeof(string));//流程主管人员头像
        dt.Columns.Add("chushiimageurl", typeof(string));//处室人员头像
        dt.Columns.Add("fcjimageurl", typeof(string));//副厂级人员头像
        dt.Columns.Add("cjimageurl", typeof(string));//厂级人员头像
        dt.Columns.Add("fbzimageurl", typeof(string));//副部长头像
        dt.Columns.Add("bzimageurl", typeof(string));//正部长人员头像
        dt.Columns.Add("fzjlimageurl", typeof(string));//副总经理人员头像
        dt.Columns.Add("bossimageurl", typeof(string));//公司领导人员头像

        u.getUserImage(dt, "lczgimageurl", "lczgname", "lczgurl");//获取流程主管人员头像
        u.getUserImage(dt, "chushiimageurl", "chushiname", "chushiurl");//获取处室人员头像头像
        u.getUserImage(dt, "fcjimageurl", "fcjname", "fcjurl");//获取副厂级人员头像
        u.getUserImage(dt, "cjimageurl", "cjname", "cjurl"); //获取厂级人员头像
        u.getUserImage(dt, "fbzimageurl", "fbzname", "fbzurl"); //获取副部长头像
        u.getUserImage(dt, "bzimageurl", "bzname", "bzurl"); //获取部长头像
        u.getUserImage(dt, "fzjlimageurl", "fzjlname", "fzjlurl"); //获取副总经理头像
        u.getUserImage(dt, "bossimageurl", "bossname", "bossurl");//获取公司领导人员头像

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //导出
    void getExportExcel(JObject jObject)
    {
        string sql = @"
SELECT tt.* FROM (
SELECT e.fullname,up.createdate,
       og.factoryname||DECODE(og.workcentername,NULL,'','/' || og.workcentername)|| DECODE(og.shiftname,NULL,'','/' || og.shiftname)|| DECODE(og.teamname,NULL,'','/' || og.teamname) fromorginfo,
       fpt.problemtype feedback_type,up.productname,up.containername,
       e2.fullname resfullname,
       og2.factoryname||DECODE(og2.workcentername,NULL,'','/' || og2.workcentername)||DECODE(og2.shiftname,NULL,'','/' || og2.shiftname)|| DECODE(og2.teamname,NULL,'','/' || og2.teamname) toorginfo,
       CASE WHEN (bpm.processdefid <> 'obj_a261aa30a89b457f900310f3d3afe86a' AND bpm.processdefid <> 'obj_95ca04cab8e1469bbd258aab2138d3d5') THEN
            CASE WHEN up.problem_status = '已关闭' THEN '6/6'
                WHEN up.problem_status <> '已关闭' AND INSTR(bpm.tasktitle,'关闭问题') = 0 THEN
                    CASE WHEN INSTR(bpm.tasktitle,'流程主管') > 0 THEN '1/6' 
                            WHEN INSTR(bpm.tasktitle,'处室') > 0 THEN '2/6' 
                            WHEN INSTR(bpm.tasktitle,'副厂') > 0 THEN '3/6'
                            WHEN INSTR(bpm.tasktitle,'厂级') > 0 THEN '4/6'
                            WHEN INSTR(bpm.tasktitle,'生产部') > 0 THEN '5/6'
                            WHEN INSTR(bpm.tasktitle,'公司领导') > 0 THEN '6/6' 
                    END 
                WHEN up.problem_status <> '已关闭' AND INSTR(bpm.tasktitle,'关闭问题') <> 0 THEN
                    CASE WHEN INSTR(bpm.prevtasktitle,'流程主管') > 0 THEN '1/6' 
                            WHEN INSTR(bpm.prevtasktitle,'处室') > 0 THEN '2/6' 
                            WHEN INSTR(bpm.prevtasktitle,'副厂') > 0 THEN '3/6'
                            WHEN INSTR(bpm.prevtasktitle,'厂级') > 0 THEN '4/6'
                            WHEN INSTR(bpm.prevtasktitle,'生产部') > 0 THEN '5/6'
                            WHEN INSTR(bpm.prevtasktitle,'公司领导') > 0 THEN '6/6' 
                    END 
                END 
        ELSE
            CASE WHEN up.problem_status = '已关闭' THEN '8/8'
                 WHEN up.problem_status <> '已关闭' AND INSTR(bpm.tasktitle,'关闭问题') = 0 THEN
                    CASE WHEN INSTR(bpm.tasktitle,'流程主管') > 0 THEN '1/8' 
                            WHEN INSTR(bpm.tasktitle,'处室') > 0 THEN '2/8' 
                            WHEN INSTR(bpm.tasktitle,'副厂') > 0 THEN '3/8'
                            WHEN INSTR(bpm.tasktitle,'厂级') > 0 THEN '4/8'
                            WHEN INSTR(bpm.tasktitle,'副部') > 0 THEN '5/8'
                            WHEN INSTR(bpm.tasktitle,'部长填写') > 0 THEN '6/8'
                            WHEN INSTR(bpm.tasktitle,'副总经理填写处理意见') > 0 THEN '7/8'
                            WHEN INSTR(bpm.tasktitle,'生产部填写处理意见') > 0 THEN '7/8'
                            WHEN INSTR(bpm.tasktitle,'公司领导填写处理意见') > 0 THEN '8/8' 
                    END 
                    WHEN up.problem_status <> '已关闭' AND INSTR(bpm.tasktitle,'关闭问题') <> 0 THEN
                           CASE WHEN INSTR(bpm.prevtasktitle,'流程主管') > 0 THEN '1/8' 
                               WHEN INSTR(bpm.prevtasktitle,'处室') > 0 THEN '2/8' 
                               WHEN INSTR(bpm.prevtasktitle,'副厂') > 0 THEN '3/8'
                               WHEN INSTR(bpm.prevtasktitle,'厂级') > 0 THEN '4/8'
                               WHEN INSTR(bpm.prevtasktitle,'副部') > 0 THEN '5/8'
                               WHEN INSTR(bpm.prevtasktitle,'部长填写') > 0 THEN '6/8'
                               WHEN INSTR(bpm.prevtasktitle,'副总经理填写处理意见') > 0 THEN '7/8'
                               WHEN INSTR(bpm.prevtasktitle,'生产部填写处理意见') > 0 THEN '7/8'
                               WHEN INSTR(bpm.prevtasktitle,'公司领导填写处理意见') > 0 THEN '8/8' 
                           END 
                    END  
        END status,
    up.problem_status,
    CASE WHEN up.problem_status = '已关闭' THEN
        '总耗时：' || extract(day FROM CAST(up.problem_close_date AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '天' ||
        extract(hour FROM CAST(up.problem_close_date AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '小时' ||
        extract(minute FROM CAST(up.problem_close_date AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '分' ||
        extract(second FROM CAST(up.problem_close_date AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '秒'
        WHEN up.problem_status = '未关闭' THEN
        '已耗时：' || extract(day FROM CAST(SYSDATE AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '天' ||
        extract(hour FROM CAST(SYSDATE AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '小时' ||
        extract(minute FROM CAST(SYSDATE AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '分' ||
        extract(second FROM CAST(SYSDATE AS TIMESTAMP) - CAST(up.createdate AS TIMESTAMP)) || '秒'
    END costtime,
    CASE WHEN up.problem_status = '已关闭' THEN  round(up.problem_close_date - up.createdate,6) ELSE round(SYSDATE - up.createdate,6) END TIME,
    CASE WHEN up.problem_status = '已关闭' THEN 1 ELSE 0 END problem__status,
    up.problem_feedback_title,
    up.lczg_emp,up.lczg_edit_date,up.lczg_result,
    up.cs_emp,up.cs_edit_date,up.cs_result,
    up.fcj_emp,up.fcj_edit_date,up.fcj_result,
    up.cj_emp,up.cj_edit_date,up.cj_result,
    up.fbz_emp,up.fbz_edit_date,up.fbz_result,
    up.bz_emp,up.bz_edit_date,up.bz_result,
    up.fzjl_emp,up.fzjl_edit_date,up.fzjl_result,
    up.boss_emp,up.boss_edit_date,up.boss_result,
    e3.fullname scbfullname,up.scb_edit_date,up.scb_result,up.resourcename
FROM bo_eu_mes_problem_feedback up
LEFT JOIN bo_eu_mes_problem_type fpt ON fpt.id = up.feedback_type
LEFT JOIN employee e ON 'MES_' || e.employeename = up.createuser
LEFT JOIN sessionvalues sv ON sv.sessionvaluesid = e.sessionvaluesid 
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid
LEFT JOIN employee e2 ON 'MES_' || e2.employeename = bpm.userid
INNER JOIN bpm_org_view og ON og.USERID = up.createuser
LEFT JOIN bpm_org_view og2 ON og2.USERID = bpm.userid
LEFT JOIN bpm_orguser_table u ON u.userid = up.createuser --问题提出人
LEFT JOIN employee e3 ON e3.employeename = SUBSTR(up.scb_emp,5,8)
WHERE (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0) 
      AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL)
      AND (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0)";

        //提出单位
        if (jObject["fromOrg"] != null && jObject["fromOrg"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["fromOrg"].ToString());
            if (arr.Count == 1)
            {
                sql += string.Format(" AND og.factoryid = '{0}'", arr[0].ToString());
            }
            else if (arr.Count == 2)
            {
                sql += string.Format(" AND og.factoryid = '{0}'", arr[0].ToString());
                sql += string.Format(" AND og.workcenterid = '{0}'", arr[1].ToString());
            }
            else if (arr.Count == 3)
            {
                sql += string.Format(" AND og.factoryid = '{0}'", arr[0].ToString());
                sql += string.Format(" AND og.workcenterid = '{0}'", arr[1].ToString());
                sql += string.Format(" AND (og.teamid = '{0}' OR og.shiftid = '{1}')", arr[2].ToString(), arr[2].ToString());
            }
            else if (arr.Count == 4)
            {
                sql += string.Format(" AND og.factoryid = '{0}'", arr[0].ToString());
                sql += string.Format(" AND og.workcenterid = '{0}'", arr[1].ToString());
                sql += string.Format(" AND og.shiftid = '{0}'", arr[2].ToString());
                sql += string.Format(" AND og.teamid = '{0}'", arr[3].ToString());
            }
        }

        //处理单位
        if (jObject["toOrg"] != null && jObject["toOrg"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["toOrg"].ToString());
            if (arr.Count == 1)
            {
                sql += string.Format(" AND og2.factoryid = '{0}'", arr[0].ToString());
            }
            else if (arr.Count == 2)
            {
                sql += string.Format(" AND og2.factoryid = '{0}'", arr[0].ToString());
                sql += string.Format(" AND og2.workcenterid = '{0}'", arr[1].ToString());
            }
            else if (arr.Count == 3)
            {
                sql += string.Format(" AND og2.factoryid = '{0}'", arr[0].ToString());
                sql += string.Format(" AND og2.workcenterid = '{0}'", arr[1].ToString());
                sql += string.Format(" AND (og2.teamid = '{0}' OR og.shiftid = '{1}')", arr[2].ToString(), arr[2].ToString());
            }
            else if (arr.Count == 4)
            {
                sql += string.Format(" AND og2.factoryid = '{0}'", arr[0].ToString());
                sql += string.Format(" AND og2.workcenterid = '{0}'", arr[1].ToString());
                sql += string.Format(" AND og2.shiftid = '{0}'", arr[2].ToString());
                sql += string.Format(" AND og2.teamid = '{0}'", arr[3].ToString());
            }
        }

        //自己相关
        if (jObject["self"] != null && jObject["self"].ToString() == "True")
        {
            if (jObject["employeeid"].ToString() != "")
            {
                sql += string.Format(" AND (e.employeeid = '{0}' OR e2.employeeid = '{1}')", jObject["employeeid"].ToString(), jObject["employeeid"].ToString());
            }
        }

        //问题分类
        if (jObject["feedbackType"].ToString() != "")
        {
            sql += string.Format(" AND fpt.problemtype LIKE '%{0}%'", jObject["feedbackType"].ToString());
        }

        //处理状态
        if (jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "处理中")
            {
                sql += string.Format(" AND up.problem_status = '未关闭'");
            }
            if (jObject["status"].ToString() == "已结束")
            {
                sql += string.Format(" AND up.problem_status = '已关闭'");
            }
        }

        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }

        //提出人
        if (jObject["createuser"].ToString() != "")
        {
            sql += string.Format(" AND u.username = '{0}'", jObject["createuser"].ToString());
        }

        //待办人
        if (jObject["dealperson"].ToString() != "")
        {
            sql += string.Format(" AND (CASE WHEN up.createuser = bpm.userid THEN e3.fullname ELSE e2.fullname END) = '{0}'", jObject["dealperson"].ToString());
        }
        
        sql += ") tt ORDER BY tt.problem__status,tt.time";

        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        foreach (DataRow dr in dtResult.Rows)
        {
            if (dr["lczg_emp"].ToString().Contains("<"))
            {
                dr["lczg_emp"] = dr["lczg_emp"].ToString().Split('<')[1].TrimEnd('>');
            }
            if (dr["cs_emp"].ToString().Contains("<"))
            {
                dr["cs_emp"] = dr["cs_emp"].ToString().Split('<')[1].TrimEnd('>');
            }
            if (dr["fcj_emp"].ToString().Contains("<"))
            {
                dr["fcj_emp"] = dr["fcj_emp"].ToString().Split('<')[1].TrimEnd('>');
            }
            if (dr["cj_emp"].ToString().Contains("<"))
            {
                dr["cj_emp"] = dr["cj_emp"].ToString().Split('<')[1].TrimEnd('>');
            }
            if (dr["fbz_emp"].ToString().Contains("<"))
            {
                dr["fbz_emp"] = dr["fbz_emp"].ToString().Split('<')[1].TrimEnd('>');
            }
            if (dr["bz_emp"].ToString().Contains("<"))
            {
                dr["bz_emp"] = dr["bz_emp"].ToString().Split('<')[1].TrimEnd('>');
            }
            if (dr["fzjl_emp"].ToString().Contains("<"))
            {
                dr["fzjl_emp"] = dr["fzjl_emp"].ToString().Split('<')[1].TrimEnd('>');
            }
            if (dr["boss_emp"].ToString().Contains("<"))
            {
                dr["boss_emp"] = dr["boss_emp"].ToString().Split('<')[1].TrimEnd('>');
            }
        }

        dtResult.Columns.Remove("time");
        dtResult.Columns.Remove("problem__status");

        dtResult.Columns["fullname"].Caption = "问题提出人";
        dtResult.Columns["createdate"].Caption = "提出时间";
        dtResult.Columns["fromorginfo"].Caption = "提出单位";
        dtResult.Columns["feedback_type"].Caption = "问题类型";
        dtResult.Columns["problem_feedback_title"].Caption = "问题描述";
        dtResult.Columns["productname"].Caption = "零件图号";
        dtResult.Columns["containername"].Caption = "批次号";
        dtResult.Columns["resourcename"].Caption = "设备";
        dtResult.Columns["problem_status"].Caption = "处理状态";
        dtResult.Columns["costtime"].Caption = "已耗时";
        dtResult.Columns["status"].Caption = "处理进度";
        dtResult.Columns["resfullname"].Caption = "待办人";
        dtResult.Columns["toorginfo"].Caption = "待办单位";

        dtResult.Columns["lczg_emp"].Caption = "流程主管人员";
        dtResult.Columns["lczg_edit_date"].Caption = "流程主管处理时间";
        dtResult.Columns["lczg_result"].Caption = "流程主管意见";

        dtResult.Columns["cs_emp"].Caption = "处室人员";
        dtResult.Columns["cs_edit_date"].Caption = "处室处理时间";
        dtResult.Columns["cs_result"].Caption = "处室处理意见";

        dtResult.Columns["fcj_emp"].Caption = "副厂级处理人员";
        dtResult.Columns["fcj_edit_date"].Caption = "副厂级处理时间";
        dtResult.Columns["fcj_result"].Caption = "副厂级处理意见";

        dtResult.Columns["cj_emp"].Caption = "厂级处理人员";
        dtResult.Columns["cj_edit_date"].Caption = "厂级处理时间";
        dtResult.Columns["cj_result"].Caption = "厂级处理意见";

        dtResult.Columns["scbfullname"].Caption = "生产部";
        dtResult.Columns["scb_edit_date"].Caption = "生产部处理时间";
        dtResult.Columns["scb_result"].Caption = "生产部处理意见";

        dtResult.Columns["fbz_emp"].Caption = "副部长";
        dtResult.Columns["fbz_edit_date"].Caption = "副部长处理时间";
        dtResult.Columns["fbz_result"].Caption = "副部长处理意见";

        dtResult.Columns["bz_emp"].Caption = "部长";
        dtResult.Columns["bz_edit_date"].Caption = "部长处理时间";
        dtResult.Columns["bz_result"].Caption = "部长处理意见";

        dtResult.Columns["fzjl_emp"].Caption = "副总经理";
        dtResult.Columns["fzjl_edit_date"].Caption = "副总经理处理时间";
        dtResult.Columns["fzjl_result"].Caption = "副总经理处理意见";

        dtResult.Columns["boss_emp"].Caption = "公司领导";
        dtResult.Columns["boss_edit_date"].Caption = "公司领导处理时间";
        dtResult.Columns["boss_result"].Caption = "公司领导处理意见";

        ExportHelper.ExportExcel(dtResult, "问题快反看板", true);
    }

    //分厂总响应率
    Result getFactoryTotalRate(JObject jObject)
    {
        string sql = @"
SELECT COUNT(up.id) total,
       SUM(CASE WHEN NVL(up.lczg_result,NVL(up.cs_result,NVL(up.fcj_result,up.cj_result))) IS NULL THEN 1 ELSE 0 END) notdealQty,
       COUNT(up.id) - SUM(CASE WHEN NVL(up.lczg_result,NVL(up.cs_result,NVL(up.fcj_result,up.cj_result))) IS NULL THEN 1 ELSE 0 END) dealQty
FROM bo_eu_mes_problem_feedback up
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid
WHERE up.lczg_emp IS NOT NULL AND up.processdefid IN ('obj_a261aa30a89b457f900310f3d3afe86a','obj_95ca04cab8e1469bbd258aab2138d3d5')
      AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL)
      AND (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0)";
        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }

        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //分厂各自响应率
    Result getFactoryRate(JObject jObject)
    {
        string sql = @"
SELECT og.factoryname,COUNT(og.factoryname) total,
       SUM(CASE WHEN NVL(up.lczg_result,NVL(up.cs_result,NVL(up.fcj_result,up.cj_result))) IS NULL THEN 1 ELSE 0 END) notdealQty,
       COUNT(og.factoryname) - SUM(CASE WHEN NVL(up.lczg_result,NVL(up.cs_result,NVL(up.fcj_result,up.cj_result))) IS NULL THEN 1 ELSE 0 END) dealQty,
       ROUND((COUNT(og.factoryname) - SUM(CASE WHEN NVL(up.lczg_result,NVL(up.cs_result,NVL(up.fcj_result,up.cj_result))) IS NULL THEN 1 ELSE 0 END))/COUNT(og.factoryname),4) * 100 dealRate
FROM bo_eu_mes_problem_feedback up
INNER JOIN bpm_org_view og ON og.USERID = up.createuser
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid
WHERE up.lczg_emp IS NOT NULL AND up.processdefid IN ('obj_a261aa30a89b457f900310f3d3afe86a','obj_95ca04cab8e1469bbd258aab2138d3d5')
      AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL)
      AND (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0)";
        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }

        sql += @"
GROUP BY og.factoryname
";

        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getFactoryRateDetailSql(JObject jObject)
    {
        string sql = @"
SELECT og.factoryname,u.username createuser,up.createdate,
       fpt.problemtype,up.problem_feedback_title,
       up.lczg_result,up.cs_result,up.fcj_result,up.cj_result,
       bpm.tasktitle,u2.username currentuser,up.problem_status,
       CASE WHEN NVL(up.lczg_result,NVL(up.cs_result,NVL(up.fcj_result,up.cj_result))) IS NULL THEN '未响应'
         ELSE '已响应'  
       END status
FROM bo_eu_mes_problem_feedback up
LEFT JOIN bo_eu_mes_problem_type fpt ON fpt.id = up.feedback_type -- 问题类型
INNER JOIN bpm_orguser_table u ON u.userid = up.createuser --问题提出人
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid --当前节点
LEFT JOIN bpm_orguser_table u2 ON u2.userid = bpm.userid --当前责任人
INNER JOIN bpm_org_view og ON og.USERID = up.createuser
WHERE up.lczg_emp IS NOT NULL AND up.processdefid IN ('obj_a261aa30a89b457f900310f3d3afe86a','obj_95ca04cab8e1469bbd258aab2138d3d5')
      AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL)
      AND (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0)";
        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
        }

        //提出单位
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND og.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        //状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "未响应")
            {
                sql += " AND NVL(up.lczg_result,NVL(up.cs_result,NVL(up.fcj_result,up.cj_result))) IS NULL";
            }
            if (jObject["status"].ToString() == "已响应")
            {
                sql += " AND NVL(up.lczg_result,NVL(up.cs_result,NVL(up.fcj_result,up.cj_result))) IS NOT NULL";
            }
        }
        return sql;
    }

    //各分厂问题处理明细
    Result getFactoryRateDetail(JObject jObject)
    {
        string sql = getFactoryRateDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //职能部门总回复率
    Result getDepartmentTotalRate(JObject jObject)
    {
        string sql = @"
SELECT COUNT(te.processinstid) total,
       SUM(CASE WHEN NVL(up.fbz_result,up.bz_result) IS NULL THEN 1 ELSE 0 END ) notdealQty,
       COUNT(te.factoryname) - SUM(CASE WHEN NVL(up.fbz_result,up.bz_result) IS NULL THEN 1 ELSE 0 END ) dealQty
FROM bo_eu_mes_problem_feedback up
INNER JOIN 
(
    SELECT t.processinstid,t.actionname,t.activityname,t.msg,t.createuser,t.createdate,ut.username fullname,u.factoryname
    FROM awsprd.feedback_wfc_comment@bpm195 t
    INNER JOIN bpm_org_view u ON u.userid = t.createuser
    LEFT JOIN bpm_orguser_table ut ON ut.userid = t.createuser
    WHERE t.activityname IN ('副部长填写处理意见') AND u.factoryname IN ('生产指挥部','工程技术部','运行保障部','质量管理部','安全保障部','采购与供应链部')
    UNION ALL
    SELECT t.processinstid,t.actionname,t.activityname,t.msg,t.createuser,t.createdate,ut.username fullname,u.factoryname
    FROM awsprd.feedback_wfhcomment@bpm195 t 
    INNER JOIN bpm_org_view u ON u.userid = t.createuser
    LEFT JOIN bpm_orguser_table ut ON ut.userid = t.createuser
    WHERE t.activityname IN ('副部长填写处理意见') AND u.factoryname IN ('生产指挥部','工程技术部','运行保障部','质量管理部','安全保障部','采购与供应链部')
) te ON te.processinstid = up.bindid
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid
WHERE up.lczg_emp IS NOT NULL AND up.processdefid IN ('obj_a261aa30a89b457f900310f3d3afe86a','obj_95ca04cab8e1469bbd258aab2138d3d5')
      AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL)
      AND (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0)";
        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }

        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    //各职能部门各自回复率
    Result getDepartmentRate(JObject jObject)
    {
        string sql = @"
SELECT te.factoryname,COUNT(te.factoryname) total,
       SUM(CASE WHEN NVL(up.fbz_result,up.bz_result) IS NULL THEN 1 ELSE 0 END ) notdealQty,
       COUNT(te.factoryname) - SUM(CASE WHEN NVL(up.fbz_result,up.bz_result) IS NULL THEN 1 ELSE 0 END ) dealQty,
       ROUND((COUNT(te.factoryname) - SUM(CASE WHEN NVL(up.fbz_result,up.bz_result) IS NULL THEN 1 ELSE 0 END ))/COUNT(te.factoryname),4) * 100 dealRate
FROM bo_eu_mes_problem_feedback up
INNER JOIN 
(
    SELECT t.processinstid,t.actionname,t.activityname,t.msg,t.createuser,t.createdate,ut.username fullname,u.factoryname
    FROM awsprd.feedback_wfc_comment@bpm195 t
    INNER JOIN bpm_org_view u ON u.userid = t.createuser
    LEFT JOIN bpm_orguser_table ut ON ut.userid = t.createuser
    WHERE t.activityname IN ('副部长填写处理意见') AND u.factoryname IN ('生产指挥部','工程技术部','运行保障部','质量管理部','安全保障部','采购与供应链部')
    UNION ALL
    SELECT t.processinstid,t.actionname,t.activityname,t.msg,t.createuser,t.createdate,ut.username fullname,u.factoryname
    FROM awsprd.feedback_wfhcomment@bpm195 t 
    INNER JOIN bpm_org_view u ON u.userid = t.createuser
    LEFT JOIN bpm_orguser_table ut ON ut.userid = t.createuser
    WHERE t.activityname IN ('副部长填写处理意见') AND u.factoryname IN ('生产指挥部','工程技术部','运行保障部','质量管理部','安全保障部','采购与供应链部')
) te ON te.processinstid = up.bindid
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid
WHERE up.lczg_emp IS NOT NULL AND up.processdefid IN ('obj_a261aa30a89b457f900310f3d3afe86a','obj_95ca04cab8e1469bbd258aab2138d3d5')
      AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL)
      AND (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0)";
        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }
        sql += @"
GROUP BY te.factoryname
";
        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    string getDepartmentRateDetailSql(JObject jObject)
    {
        string sql = @"
SELECT te.factoryname departmentname,og.factoryname,u.username createuser,up.createdate,
       fpt.problemtype,up.problem_feedback_title,
       up.fbz_result,up.bz_result,
       bpm.tasktitle,u2.username currentuser,up.problem_status,
       CASE WHEN NVL(up.fbz_result,up.bz_result) IS NULL THEN '未响应'
         ELSE '已响应'  
       END status
FROM bo_eu_mes_problem_feedback up
LEFT JOIN bo_eu_mes_problem_type fpt ON fpt.id = up.feedback_type -- 问题类型
LEFT JOIN bpm_orguser_table u ON u.userid = up.createuser --问题提出人
LEFT JOIN awsprd.feedback_wfctask@bpm195 bpm ON bpm.processinstid = up.bindid --当前节点
LEFT JOIN bpm_orguser_table u2 ON u2.userid = bpm.userid --当前责任人
LEFT JOIN bpm_org_view og ON og.USERID = up.createuser
INNER JOIN 
(
    SELECT t.processinstid,t.actionname,t.activityname,t.msg,t.createuser,t.createdate,ut.username fullname,u.factoryname
    FROM awsprd.feedback_wfc_comment@bpm195 t
    INNER JOIN bpm_org_view u ON u.userid = t.createuser
    LEFT JOIN bpm_orguser_table ut ON ut.userid = t.createuser
    WHERE t.activityname IN ('副部长填写处理意见') AND u.factoryname IN ('生产指挥部','工程技术部','运行保障部','质量管理部','安全保障部','采购与供应链部')
    UNION ALL
    SELECT t.processinstid,t.actionname,t.activityname,t.msg,t.createuser,t.createdate,ut.username fullname,u.factoryname
    FROM awsprd.feedback_wfhcomment@bpm195 t 
    INNER JOIN bpm_org_view u ON u.userid = t.createuser
    LEFT JOIN bpm_orguser_table ut ON ut.userid = t.createuser
    WHERE t.activityname IN ('副部长填写处理意见') AND u.factoryname IN ('生产指挥部','工程技术部','运行保障部','质量管理部','安全保障部','采购与供应链部')
) te ON te.processinstid = up.bindid
WHERE up.lczg_emp IS NOT NULL AND up.processdefid IN ('obj_a261aa30a89b457f900310f3d3afe86a','obj_95ca04cab8e1469bbd258aab2138d3d5')
      AND NOT (up.problem_status = '未关闭' AND bpm.id IS NULL)
      AND (bpm.tasktitle IS NULL OR bpm.tasktitle IS NOT NULL AND INSTR(bpm.tasktitle,'上报问题') = 0)";
        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            if (arr.Count >= 2)
            {
                sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
                sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
            }
        }

        //职能部门
        if (jObject["factoryname"] != null && jObject["factoryname"].ToString() != "")
        {
            sql += string.Format(" AND te.factoryname = '{0}'", jObject["factoryname"].ToString());
        }

        //状态
        if (jObject["status"] != null && jObject["status"].ToString() != "")
        {
            if (jObject["status"].ToString() == "未响应")
            {
                sql += " AND NVL(up.fbz_result,up.bz_result) IS NULL";
            }
            if (jObject["status"].ToString() == "已响应")
            {
                sql += " AND NVL(up.fbz_result,up.bz_result) IS NOT NULL";
            }
        }

        sql += " ORDER BY up.problem_feedback_title";
        return sql;
    }


    Result getDepartmentRateDetail(JObject jObject)
    {
        string sql = getDepartmentRateDetailSql(jObject);
        uMESPagingDataDTO dto = dal.GetPagingDataIns(sql, (int)jObject["currentPage"], (int)jObject["pageSize"]);
        DataTable dt = dto.DBTable;

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dto.RowCount);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    Result getLast10Data(JObject jObject)
    {
        string sql = @"
SELECT t3.username,t3.total,nvl(t4.notdealQty,0) notdealQty,t3.total - nvl(t4.notdealQty,0) dealQty,
       ROUND((t3.total - nvl(t4.notdealQty,0))/t3.total,4) * 100 dealRate
FROM (
  SELECT t2.createuser,u.username,COUNT(1) total
  FROM (
    SELECT DISTINCT t1.processinstid,t1.createuser
    FROM 
    (
      SELECT t.processinstid,t.createuser
      FROM awsprd.feedback_wfc_comment@bpm195 t
      LEFT JOIN bo_eu_mes_problem_feedback up ON up.bindid = t.processinstid
      WHERE t.activityname <> '维护问题类型' AND SUBSTR(t.createuser,1,3) = 'MES'
            AND t.activityname NOT IN ('上报问题','问题提出人关闭问题','生产部填写处理意见','公司领导填写处理意见') ";

        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
        }

        sql += @"
      UNION ALL
      SELECT t.processinstid,t.createuser
      FROM awsprd.feedback_wfhcomment@bpm195 t 
      LEFT JOIN bo_eu_mes_problem_feedback up ON up.bindid = t.processinstid
      WHERE t.activityname <> '维护问题类型'
            AND t.activityname NOT IN ('上报问题','问题提出人关闭问题','生产部填写处理意见','公司领导填写处理意见') ";

        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
        }

        sql += @"
    ) t1
  ) t2
  LEFT JOIN bpm_orguser_table u ON u.userid = t2.createuser
  GROUP BY t2.createuser,u.username
) t3
LEFT JOIN 
(
  SELECT t2.createuser,u.username,COUNT(1) notdealQty
  FROM (
    SELECT DISTINCT t1.processinstid,t1.createuser
    FROM 
    (
      SELECT t.processinstid,t.createuser
      FROM awsprd.feedback_wfc_comment@bpm195 t
      LEFT JOIN bo_eu_mes_problem_feedback up ON up.bindid = t.processinstid
      WHERE t.activityname <> '维护问题类型' AND SUBSTR(t.createuser,1,3) = 'MES'
            AND t.activityname NOT IN ('上报问题','问题提出人关闭问题','生产部填写处理意见','公司领导填写处理意见')
            AND t.msg = '<font color=red>超时自动执行</font>' ";

        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
        }

        sql += @"
      UNION ALL
      SELECT t.processinstid,t.createuser
      FROM awsprd.feedback_wfhcomment@bpm195 t 
      LEFT JOIN bo_eu_mes_problem_feedback up ON up.bindid = t.processinstid
      WHERE t.activityname <> '维护问题类型'
            AND t.activityname NOT IN ('上报问题','问题提出人关闭问题','生产部填写处理意见','公司领导填写处理意见')
            AND t.msg = '<font color=red>超时自动执行</font>' ";

        //提出时间
        if (jObject["createdate"] != null && jObject["createdate"].ToString() != "")
        {
            JArray arr = JArray.Parse(jObject["createdate"].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) >= TO_DATE('{0}','YYYY-MM-DD') ", arr[0].ToString());
            sql += string.Format(" AND TRUNC(up.createdate) <= TO_DATE('{0}','YYYY-MM-DD') ", arr[1].ToString());
        }

        sql += @"   
    ) t1
  ) t2
  LEFT JOIN bpm_orguser_table u ON u.userid = t2.createuser
  GROUP BY t2.createuser,u.username
) t4 ON t4.createuser = t3.createuser
ORDER BY nvl(t4.notdealQty,0) DESC
";

        DataTable dt = dal.GetDataTable(sql);

        foreach (DataColumn col in dt.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }

        Result r = new Result();
        r.data = dt;
        r.total = Convert.ToInt32(dt.Rows.Count);
        r.meta = new Result.Meta(200, "请求成功");
        return r;
    }

    void exportFactoryRateDetail(JObject jObject)
    {
        string sql = getFactoryRateDetailSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);

        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "分厂";
        dtResult.Columns["createuser"].Caption = "提出人";
        dtResult.Columns["createdate"].Caption = "提出时间";
        dtResult.Columns["problemtype"].Caption = "问题类型";
        dtResult.Columns["problem_feedback_title"].Caption = "问题描述";
        dtResult.Columns["lczg_result"].Caption = "流程主管意见";
        dtResult.Columns["cs_result"].Caption = "处室意见";
        dtResult.Columns["fcj_result"].Caption = "副厂级意见";
        dtResult.Columns["cj_result"].Caption = "厂级意见";
        dtResult.Columns["tasktitle"].Caption = "当前节点";
        dtResult.Columns["currentuser"].Caption = "待办人";
        dtResult.Columns["problem_status"].Caption = "是否关闭";
        dtResult.Columns["status"].Caption = "状态";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + jObject["status"].ToString() + "明细", true);
    }

    void exportDepartmentRateDetail(JObject jObject)
    {
        string sql = getDepartmentRateDetailSql(jObject);
        DataTable dtResult = dal.GetDataTable(sql);
        foreach (DataColumn col in dtResult.Columns)
        {
            col.ColumnName = col.ColumnName.ToLower();
        }
        dtResult.Columns["factoryname"].Caption = "分厂";
        dtResult.Columns["createuser"].Caption = "提出人";
        dtResult.Columns["createdate"].Caption = "提出时间";
        dtResult.Columns["problemtype"].Caption = "问题类型";
        dtResult.Columns["problem_feedback_title"].Caption = "问题描述";
        dtResult.Columns["lczg_result"].Caption = "流程主管意见";
        dtResult.Columns["cs_result"].Caption = "处室意见";
        dtResult.Columns["fcj_result"].Caption = "副厂级意见";
        dtResult.Columns["cj_result"].Caption = "厂级意见";
        dtResult.Columns["tasktitle"].Caption = "当前节点";
        dtResult.Columns["currentuser"].Caption = "待办人";
        dtResult.Columns["problem_status"].Caption = "是否关闭";
        dtResult.Columns["status"].Caption = "状态";
        ExportHelper.ExportExcel(dtResult, jObject["factoryname"].ToString() + jObject["status"].ToString() + "明细", true);
    }
}